﻿
CREATE PROCEDURE dbo.s_CreditInfo_Fill
	@ApplID int
AS

DECLARE @DatePulled smalldatetime SELECT @DatePulled = DatePulled FROM  t_CreditInfo WHERE (ApplicantID = @ApplID)
DECLARE @1234s TABLE(Code int,MonthsSynce int,AccountType varchar(64))
DECLARE @Codes TABLE(Code int,AccountType varchar(64))

INSERT INTO @1234s
SELECT t_1234s.Code,
			 DATEDIFF(mm, t_1234s.Date, t_CreditInfo.DatePulled) AS MonthsSynce,
			 v_CreditLiability._AccountType AS AccountType
FROM  t_1234s INNER JOIN
               v_CreditLiability ON t_1234s.CreditLiabilityID = v_CreditLiability.ID INNER JOIN
               t_CreditInfo ON v_CreditLiability.ApplicantID = t_CreditInfo.ApplicantID
WHERE (v_CreditLiability.ApplicantID = @ApplID)

INSERT INTO @Codes
SELECT _CURRENT_RATING_Code - 1 AS Code, _AccountType
FROM  v_CreditLiability
WHERE (ApplicantID = @ApplID) AND (_CURRENT_RATING_Code - 1 > 0)

UPDATE t_CreditInfo
	SET TradeLinesCount_30  = (SELECT COUNT(*) FROM @1234s WHERE Code = 1 AND MonthsSynce <= 12)+(SELECT Count(*) FROM @Codes WHERE Code = 1),
			TradeLinesCount_60  = (SELECT COUNT(*) FROM @1234s WHERE Code = 2 AND MonthsSynce <= 12)+(SELECT Count(*) FROM @Codes WHERE Code = 2),
			TradeLinesCount_90  = (SELECT COUNT(*) FROM @1234s WHERE Code = 3 AND MonthsSynce <= 12)+(SELECT Count(*) FROM @Codes WHERE Code = 3),
			TradeLinesCount_120 = (SELECT COUNT(*) FROM @1234s WHERE Code = 4 AND MonthsSynce <= 12)+(SELECT Count(*) FROM @Codes WHERE Code = 4),
			
			MortgageCount = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,DEFAULT,DEFAULT,DEFAULT,'Mortgage',DEFAULT),
			MortgageMonthReviewedMax = (SELECT MAX(_MonthsReviewedCount)FROM v_CreditLiability WHERE ApplicantID = @ApplID AND _AccountType = 'Mortgage'),
			
			MortgageHistory_30 = (SELECT COUNT(*) FROM @1234s WHERE Code = 1 AND MonthsSynce <= 12 AND AccountType = 'Mortgage')+(SELECT Count(*) FROM @Codes WHERE Code = 1 AND AccountType = 'Mortgage'),
			MortgageHistory_60 = (SELECT COUNT(*) FROM @1234s WHERE Code = 2 AND MonthsSynce <= 12 AND AccountType = 'Mortgage')+(SELECT Count(*) FROM @Codes WHERE Code = 2 AND AccountType = 'Mortgage'),
			MortgageHistory_90 = (SELECT COUNT(*) FROM @1234s WHERE Code = 3 AND MonthsSynce <= 12 AND AccountType = 'Mortgage')+(SELECT Count(*) FROM @Codes WHERE Code = 3 AND AccountType = 'Mortgage'),
			MortgageHistory_120 = (SELECT COUNT(*) FROM @1234s WHERE Code = 4 AND MonthsSynce <= 12 AND AccountType = 'Mortgage')+(SELECT Count(*) FROM @Codes WHERE Code = 4 AND AccountType = 'Mortgage'),
			
			TradeLinesTotal = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT),
			TradeLinesCount_ActiveOpen_03 = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,3,DEFAULT,'Open',DEFAULT,DEFAULT),
			TradeLinesCount_ActiveOpen_06 = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,6,DEFAULT,'Open',DEFAULT,DEFAULT),
			TradeLinesCount_ActiveOpen_12 = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,12,DEFAULT,'Open',DEFAULT,DEFAULT),
			TradeLinesCount_ActiveOpen_24 = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,24,DEFAULT,'Open',DEFAULT,DEFAULT),
			TradeLinesCount_ActiveOpen_12_1000 = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,12,1000,'Open',DEFAULT,DEFAULT),
			TradeLinesCount_ActiveOpen_12_5000 = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,12,5000,'Open',DEFAULT,DEFAULT),
			TradeLinesCount_MR_24_5000 = dbo.credit_TradeLinesByAmountAndMR(@ApplID,DEFAULT,24,5000,DEFAULT,DEFAULT,DEFAULT),

			ChargeOffsCount = (SELECT COUNT(*)FROM v_CreditLiability WHERE ApplicantID = @ApplID AND _DerogatoryDataIndicator = 'Y' AND _AccountStatusType = 'Open' AND DATEDIFF(mm, _LastActivityDate, @DatePulled) < 24),
			ChargeOffsAmount = dbo.credit_ChargeOffsAmount(@ApplID,DEFAULT,DEFAULT),
						
			PaymentDelayAverage = ISNULL(ROUND(dbo.credit_RatingAverage(@ApplID,DEFAULT,DEFAULT)*30,0),0)

WHERE ApplicantID = @ApplID

UPDATE  t_CreditInfo
SET		Chapter7_FileDate = dbo.credit_BK_7_FileDate(@ApplID, DEFAULT),
			Chapter7_SatisfiedDate = dbo.credit_BK_7_DispositionDate(@ApplID, DEFAULT),
			Chapter7_Status = dbo.credit_BK_7_Status(@ApplID, DEFAULT),
			Chapter7_Count = dbo.credit_BK_7_Count(@ApplID, DEFAULT)
WHERE ApplicantID = @ApplID AND Chapter7_Status > 0

UPDATE  t_CreditInfo
SET		Chapter13_FileDate = dbo.credit_BK_13_FileDate(@ApplID, DEFAULT),
			Chapter13_SatisfiedDate = dbo.credit_BK_13_DispositionDate(@ApplID, DEFAULT),
			Chapter13_Status = dbo.credit_BK_13_Status(@ApplID, DEFAULT),
			Chapter13_Count = dbo.credit_BK_13_Count(@ApplID, DEFAULT)
WHERE ApplicantID = @ApplID AND Chapter13_Status > 0

UPDATE  t_CreditInfo
SET		ForeclosureNOD_FileDate = dbo.credit_FC_FileDate(@ApplID, DEFAULT),
			ForeclosureNOD_SatisfiedDate = dbo.credit_FC_DispositionDate(@ApplID, DEFAULT),
			ForeclosureNOD_Status = dbo.credit_FC_Status(@ApplID, DEFAULT),
			ForeclosureNOD_Count = dbo.credit_FC_Count(@ApplID, DEFAULT)
WHERE ApplicantID = @ApplID AND ForeclosureNOD_Status > 0

RETURN 

